IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GenMSSQLMemoryWork' AND type = 'P')
     DROP PROCEDURE dbo.GenMSSQLMemoryWork
GO

CREATE PROCEDURE dbo.GenMSSQLMemoryWork
     @PackageExecutionId tinyint,
     @CleanupFlag        tinyint = 0
AS
/*
 *
 * GenMSSQLMemoryWork
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/03/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @TableExistsFlag tinyint,
             @DropTableFlag   tinyint,
             @CreateTableFlag tinyint,
             @RC              int

     CREATE TABLE #Output ( RecCnt int NOT NULL )

     -- Get whether the table exists
     IF EXISTS ( SELECT 'X' FROM tempdb.sys.tables WHERE name = 'tempMSSQLRunningMachine' AND schema_id = 1 AND type = 'U' )
     BEGIN
          PRINT 'Process Table Exists'
          SET @TableExistsFlag = 1
     END
     ELSE 
     BEGIN
          SET @TableExistsFlag = 0
     END

     -- What mode are we running in..
     IF ( @CleanupFlag = 1 ) 
     BEGIN -- Perform cleanup
          PRINT 'Cleanup Process'
          DECLARE @Cmd           varchar(255),
                  @RecCnt        int

          IF ( @TableExistsFlag = 0 )
          BEGIN -- we don't need to cleanup since there are no records and no table
               DROP TABLE #Output
               PRINT 'No cleanup required.'
               RETURN 
          END
          ELSE
          BEGIN -- A table exists, we will see how much cleanup is needed, just 
                -- a removal of records or a table too..
               -- Remove records for this PackageExecutionId
               PRINT 'Cleaning up records for this process.'

               DELETE FROM tempdb.dbo.tempMSSQLRunningMachine WHERE PackageExecutionId = @PackageExecutionId

               -- Lets see if there is any other process that might have used the table and is in process.
               INSERT #Output ( RecCnt ) EXEC ( 'SELECT COUNT(*) FROM tempdb.dbo.tempMSSQLRunningMachine WHERE CrDt > DATEADD( mi, -15, CURRENT_TIMESTAMP )' )
               SELECT @RecCnt = RecCnt FROM #Output
               SELECT @RecCnt = ISNULL( @RecCnt, 0 )

               IF ( @RecCnt > 0 )
               BEGIN -- We have a record less than 15 
                    SET @DropTableFlag = 0
               END
               ELSE
               BEGIN
                    SET @DropTableFlag = 1
               END
          END
     END
     ELSE 
     BEGIN
          PRINT 'Build Process'
          IF ( @TableExistsFlag = 1 )
          BEGIN -- Table does exist, determine if this is a drop/create scenario
               INSERT #Output ( RecCnt ) EXEC ( 'SELECT COUNT(*) FROM tempdb.dbo.tempMSSQLRunningMachine WHERE CrDt > DATEADD( mi, -15, CURRENT_TIMESTAMP )' )
               SELECT @RecCnt = RecCnt FROM #Output
               SELECT @RecCnt = ISNULL( @RecCnt, 0 )

               IF ( @RecCnt > 0 )
               BEGIN -- We have a record less than 15m, so we leave the table alone.
                    SET @DropTableFlag = 0
                    SET @CreateTableFlag = 0
               END
               ELSE
               BEGIN -- We do a drop/create
                    SET @DropTableFlag = 1
                    SET @CreateTableFlag = 1
               END
          END
          ELSE
          BEGIN -- table doesn't exist so no drop
               SET @CreateTableFlag = 1
          END
     END

     IF ( @DropTableFlag = 1 )
     BEGIN
          PRINT 'Dropping Table'
          EXEC ( 'DROP TABLE tempdb.dbo.tempMSSQLRunningMachine' )

          IF EXISTS ( SELECT 'X' FROM tempdb.sys.tables WHERE name = 'tempMSSQLRunningMachine' AND schema_id = 1 AND type = 'U' )
          BEGIN
               PRINT 'Table Drop Failed'
               IF ( @CreateTableFlag = 1 )
               BEGIN
                    PRINT 'Overriding Create Table Flag'
                    SET @CreateTableFlag = 0
               END
               ELSE
               BEGIN
                    DROP TABLE #Output
                    RAISERROR( 'Error! Process working table tempMSSQLRunningMachine still exists!', 16, 1 )
                    RETURN 1
               END 
          END     
     END

     IF ( @CreateTableFlag = 1 )
     BEGIN -- Create it
          PRINT 'Creating Table'
          EXEC ( 'CREATE TABLE tempdb.dbo.tempMSSQLRunningMachine ( PackageExecutionId BIGINT NOT NULL, SrvrInstncId INT NOT NULL, MachineName varchar(128) NOT NULL, CrDt datetime NULL )' )

          -- Immediately insert record
          PRINT 'Inserting Process Record'
          INSERT tempdb.dbo.tempMSSQLRunningMachine
               ( PackageExecutionId,
                 SrvrInstncId,
                 MachineName, 
                 CrDt )
          SELECT @PackageExecutionId,
                 -1, 
                 '',
                 CURRENT_TIMESTAMP

          -- Verify working table existence
          IF NOT EXISTS ( SELECT 'X' FROM tempdb.sys.tables WHERE name = 'tempMSSQLRunningMachine' AND schema_id = 1 AND type = 'U' )
          BEGIN
               DROP TABLE #Output
               RAISERROR( 'Error! Process working table tempMSSQLRunningMachine does not exist!', 16, 1 )
               RETURN 1
          END     
     END

     DROP TABLE #Output
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Creates, recreates, or deletes the persisted temp table in tempdb used for setting memory limits for MSSQL Instances in a cluster.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = GenMSSQLMemoryWork;
GO
